import pandas as pd
from typing import Dict, List
from interfaces.transformers import IDataPivot
from constants import LINE_NUMBER, BASE_COLS


class DataPivotTransformer(IDataPivot):
    def __init__(self, default_source_data: str):
        self.default_source_data = default_source_data

    def perform_pivot(self, config: Dict) -> pd.DataFrame:
        sheet_name: str = config.get('sheet_name', '')
        base_cols: List[str] = config.get('base_cols', BASE_COLS)
        merge_keys: List[str] = list(config.get('merge_keys', []))
        group_dims: List[str] = list(config.get('group_dims', []))
        value_cols: List[str] = list(config.get('value_cols', []))

        if not sheet_name or not merge_keys or not group_dims or not value_cols:
            return pd.DataFrame()

        # 读取并标准化列
        df = pd.read_excel(self.default_source_data, sheet_name=sheet_name, dtype=str, keep_default_na=False, na_values=[])
        if df is None or df.empty:
            return pd.DataFrame()
        df.columns = [str(c).strip() for c in df.columns]
        df = df.loc[:, ~df.columns.duplicated()]

        # 校验所需列存在
        for col in merge_keys + group_dims:
            if col not in df.columns:
                return pd.DataFrame()
        value_cols = [c for c in value_cols if c in df.columns]
        if not value_cols:
            return pd.DataFrame()

        # 基础列与基础数据（按连接键去重保留最后）
        base_available = [c for c in base_cols if c in df.columns]
        base_data = df[base_available].astype(str).drop_duplicates(subset=merge_keys, keep='last')

        # 透视：index 为连接键，columns 为分组维度（多维），values 为值列（多列）
        pivoted = df.pivot_table(index=merge_keys, columns=group_dims, values=value_cols, aggfunc='first')
        if pivoted.empty:
            return pd.DataFrame()

        # 维度排序函数：行号按自然数；其他按出现顺序
        def _num_key(s: str):
            try:
                return (0, int(s))
            except Exception:
                return (1, s)

        dim_orders: Dict[str, List[str]] = {}
        for dim in group_dims:
            if dim not in df.columns:
                dim_orders[dim] = []
                continue
            if dim == LINE_NUMBER:
                dim_orders[dim] = sorted(df[dim].unique().tolist(), key=_num_key)
            else:
                dim_orders[dim] = list(dict.fromkeys(df[dim].tolist()))

        # 值列顺序按提供顺序过滤存在列
        value_order: List[str] = [c for c in value_cols]

        # 构造按顺序的列键集合
        # pivoted.columns 形状为 MultiIndex: (value_col, dim1_val, dim2_val, ...)
        new_cols = []
        def _product(prefix: List[str], dims_left: List[str]):
            # 递归生成维度值组合，遵循 dim_orders 顺序
            if not dims_left:
                return [tuple(prefix)]
            dim = dims_left[0]
            combos = []
            for v in dim_orders.get(dim, []):
                combos.extend(_product(prefix + [v], dims_left[1:]))
            return combos

        dim_value_combos = _product([], group_dims)
        # 列顺序：先按维度组合遍历，再在每个组合内按值列顺序排列（例如每个行号内按 PCTPT、PCDAT、PCTIM）
        for combo in dim_value_combos:
            for val in value_order:
                key = (val,) + combo
                if key in pivoted.columns:
                    new_cols.append(key)

        ordered = pivoted[new_cols].copy()
        # 重命名列：维度值拼接在前，值列名在后
        def _rename(col_tuple: tuple) -> str:
            val = col_tuple[0]
            dim_vals = col_tuple[1:]
            return f"{'_'.join(map(str, dim_vals))}_{val}"
        ordered.columns = [_rename(t) for t in ordered.columns]

        # 合并基础数据，输出列顺序：BASE + 透视列
        ordered = ordered.reset_index()
        result = base_data.merge(ordered, on=merge_keys, how='left')
        desired_cols = base_available + [c for c in ordered.columns if c not in merge_keys]
        result = result[[c for c in desired_cols if c in result.columns]]
        return result